# coding:utf-8

import os
import urllib
from datetime import date, timedelta, datetime
from collections import namedtuple
from itertools import groupby

from sqlalchemy.sql import text
from wechatpy.exceptions import WeChatClientException

from common.schedule.opc_celery import celery_app
from common.utils import DB
from common.wechat_util import (
    send_template_message, grequest_send, update_users_tag,
    get_user_tags, update_wxuser_remark, create_user_tags
)


def get_timestamp_range(days):
    """
    created by chenhj at 2017/7/31
    sample: days为1表示取昨天0点到24点之间的时间戳范围, 2表示前天0点到24点, 以此类推
    """
    today = date.today()
    start_date = today - timedelta(days=days)
    end_date = start_date + timedelta(days=1)
    return namedtuple('TimeStamp', 'start end')(
        start_date.strftime('%s'), end_date.strftime('%s')
    )


def wx_send(result, gen_message_func, template_id, url, send_count=50, tag_id=None):
    """
    created by chenhj at 2017/8/2
    批量发送微信模板消息, 若tag_id不为None, 则同时需要为这些用户加标签
    """
    def update_wxuser(openids):
        """
        created by chenhj at 2017/8/2
        更新微信用户表信息
        """
        if tag_id:
            update_wxuser_info_sql = text("""
                update wxuser set tagid_list=json_merge(tagid_list, ':tag_id')
                 where openid in :openids and not json_contains(tagid_list, ':tag_id');
            """)
            with DB() as session:
                # 发送请求给微信, 给用户新增标签
                ret = update_users_tag(list(openids), tag_id)
                # 若成功, 则把本地的微信用户信息也加上标签
                if ret['errcode'] == 0:
                    session.execute(
                        update_wxuser_info_sql,
                        {
                            'tag_id': tag_id,
                            'openids': tuple(openids),
                        })
                openid_set.clear()

    req_set = set()
    openid_set = set()
    for each in result:
        req_set.add(send_template_message(**gen_message_func(each, template_id, url)))
        openid_set.add(each.openid)
        if len(req_set) == send_count:
            grequest_send(req_set)
            req_set.clear()
            # 给这些用户打上标签
            update_wxuser(openid_set)
    if req_set:
        grequest_send(req_set)
        # 给这些用户打上标签
        update_wxuser(openid_set)


@celery_app.task(name='first_invite_binding')
def first_invite_binding():
    """
    created by chenhj at 2017/8/2
    初次邀请用户绑定(用户关注15分钟内)
    """
    print('send first invite binding message...')

    wxuser_tag_sql = """
        select tag_id from wechat_tag where tag_name='Y已邀请绑定';
    """

    now = datetime.now()
    start_time = now - timedelta(minutes=15)

    start_timestamp = start_time.strftime('%s')
    end_timestamp = now.strftime('%s')

    # 取未绑定的微信列表
    unbind_wxuser_sql = """
        select distinct openid, subscribe_time from wxuser
        where subscribe = 1
         and subscribe_time BETWEEN {start_timestamp} and {end_timestamp}
         and openid not in
          (select distinct wx_openid from user where wx_openid != '')
    """.format(
        start_timestamp=start_timestamp, end_timestamp=end_timestamp
    )

    # 测试用
    # unbind_wxuser_sql = """
    #     select 'o1DGe1OMj8V0lZd0ppgMfPDrE1ao' as openid, subscribe_time
    #      from wxuser limit 1
    # """

    select_wx_template_sql = """
        select template_code from wechat_template where code='new_order'
    """

    def gen_message(user, template_id, url):
        """
        created by chenhj at 2017/8/2

        """
        return {
            'openid': user.openid,
            'template_id': template_id,
            'data': {
                "first": {
                    "value": "您有1条LED屏安装订单未查看，过期将失效！",
                    "color": "#173177"
                },
                "keyword1": {
                    "value": "LED显示屏安装",
                    "color": "#173177"
                },
                "keyword2": {
                    "value": datetime.fromtimestamp(user.subscribe_time).strftime('%F %T'),
                    "color": "#173177"
                },
                "keyword3": {
                    "value": "议价",
                    "color": "#173177"
                },
                "keyword4": {
                    "value": "抢单成功后可见",
                    "color": "#173177"
                },
                "remark": {
                    "value": "\n客户在线, 速戳立即抢单↓↓↓",
                    "color": "#4785F9"
                }
            },
            'url': url
        }

    with DB() as session:
        url = os.environ.get('wph_host') or ''
        tag = session.execute(wxuser_tag_sql).fetchone()

        result = session.execute(unbind_wxuser_sql).fetchall()
        wx_template = session.execute(select_wx_template_sql).fetchone()
        template_id = wx_template.template_code

        wx_send(result, gen_message, template_id, url, tag_id=tag.tag_id)


@celery_app.task(name='invite_binding')
def invite_binding(days):
    """
    created by chenhj at 2017/7/31
    邀请工程商绑定微信账号(第2天, 第3天, 第4天)
    """
    print('send invite binding message...')

    # 取对应的用户标签id
    # 如果传入的days为1, 则表示是前一天, 因为使用的是days+1(2)
    wxuser_tag_sql = """
        select tag_id from wechat_tag where tag_name='已邀请绑定{}'
    """.format(days+1)

    # 取时间戳范围
    timestamp_range = get_timestamp_range(days)
    # 取未绑定的微信列表
    unbind_wxuser_sql = """
        select distinct openid, subscribe_time from wxuser
        where subscribe = 1
         and subscribe_time BETWEEN {start_timestamp} and {end_timestamp}
         and openid not in
          (select distinct wx_openid from user where wx_openid != '')
    """.format(
        start_timestamp=timestamp_range.start, end_timestamp=timestamp_range.end
    )

    # 测试用
    # unbind_wxuser_sql = """
    #     select 'o1DGe1OMj8V0lZd0ppgMfPDrE1ao' as openid, subscribe_time
    #      from wxuser limit 1
    # """

    select_wx_template_sql = """
        select template_code from wechat_template where code='new_order'
    """

    def gen_message(user, template_id, url):
        """
        created by chenhj at 2017/8/2

        """
        return {
            'openid': user.openid,
            'template_id': template_id,
            'data': {
                "first": {
                    "value": "您有1条LED屏安装订单未查看，过期将失效！",
                    "color": "#173177"
                },
                "keyword1": {
                    "value": "LED显示屏安装",
                    "color": "#173177"
                },
                "keyword2": {
                    "value": datetime.fromtimestamp(user.subscribe_time).strftime('%F %T'),
                    "color": "#173177"
                },
                "keyword3": {
                    "value": "议价",
                    "color": "#173177"
                },
                "keyword4": {
                    "value": "抢单成功后可见",
                    "color": "#173177"
                },
                "remark": {
                    "value": "\n客户在线, 速戳立即抢单↓↓↓",
                    "color": "#4785F9"
                }
            },
            'url': url
        }

    with DB() as session:
        url = os.environ.get('wph_host') or ''
        tag = session.execute(wxuser_tag_sql).fetchone()

        result = session.execute(unbind_wxuser_sql).fetchall()
        wx_template = session.execute(select_wx_template_sql).fetchone()
        template_id = wx_template.template_code

        wx_send(result, gen_message, template_id, url, tag_id=tag.tag_id)


@celery_app.task(name='invite_improve')
def invite_improve():
    """
    created by chenhj at 2017/8/3
    邀请未完善信息且未邀请过完善信息的用户进行信息完善
    """
    print('send invite improve message...')

    # 取对应的用户标签id
    wxuser_tag_sql = """
        select tag_id from wechat_tag where tag_name='邀请认证'
    """

    # 取未完善信息的微信列表
    unimprove_wxuser_sql = """
        select distinct wx_openid as openid, user.nickname from user join wxuser
        where user.status = 0 and wx_openid != '' and wxuser.openid = user.wx_openid and user.black = FALSE 
         and not json_contains(wxuser.tagid_list, '{tag_id}')
    """

    # 测试用
    # unimprove_wxuser_sql = """
    #     select distinct wx_openid as openid, user.nickname from user join wxuser
    #     where user.status = 0 and wx_openid != '' and wxuser.openid = user.wx_openid
    #      and not json_contains(wxuser.tagid_list, '{tag_id}')
    #      and wx_openid='o1DGe1OMj8V0lZd0ppgMfPDrE1ao'
    # """

    select_wx_template_sql = """
        select template_code from wechat_template where code='invite_improve'
    """

    def gen_message(user, template_id, url):
        """
        created by chenhj at 2017/8/2

        """
        return {
            'openid': user.openid,
            'template_id': template_id,
            'data': {
                "first": {
                    "value": "您的资料尚未完善，请立即完善以免影响您获得订单推送！",
                    "color": "#173177"
                },
                "keyword1": {
                    "value": user.nickname,
                    "color": "#173177"
                },
                "keyword2": {
                    "value": "服务资料待完善",
                    "color": "#173177"
                },
                "remark": {
                    "value": "\n升级铜牌会员，需要您的资料通过审核↓↓↓",
                    "color": "#4785F9"
                }
            },
            'url': url
        }

    with DB() as session:
        domain = os.environ.get('wph_host') or ''
        url = urllib.parse.urljoin(domain, '#user/JoinSupplier')
        tag = session.execute(wxuser_tag_sql).fetchone()

        result = session.execute(unimprove_wxuser_sql.format(tag_id=tag.tag_id)).fetchall()
        wx_template = session.execute(select_wx_template_sql).fetchone()
        template_id = wx_template.template_code

        wx_send(result, gen_message, template_id, url, tag_id=tag.tag_id)


@celery_app.task(name='sync_binded_wx_user')
def sync_binded_wx_user():
    """
    created by chenhj at 2017/8/11
    把已绑定的微信用户加上省份的标签, 并在名称上加入手机号
    """
    # 先同步所有标签
    all_tags = {i['name']: i['id'] for i in get_user_tags()['tags']}

    select_binded_wx_user_sql = """
        select distinct wx_openid as openid, user.mobile, user.prov from user join wxuser
        where wx_openid != '' and wxuser.openid = user.wx_openid and wxuser.subscribe = 1
         and not json_contains(wxuser.tagid_list, '{tag_id}')
         order by user.prov
    """

    # 测试用
    # select_binded_wx_user_sql = """
    #     select distinct wx_openid as openid, user.mobile, user.prov from user join wxuser
    #     where wx_openid = 'o1DGe1OMj8V0lZd0ppgMfPDrE1ao' and wxuser.openid = user.wx_openid and wxuser.subscribe = 1
    #      and not json_contains(wxuser.tagid_list, '{tag_id}')
    #      order by user.prov
    # """

    update_wxuser_info_sql = text("""
        update wxuser set tagid_list=json_merge(tagid_list, ':tag_id')
         where openid in :openids and not json_contains(tagid_list, ':tag_id');
    """)

    # 把该用户更新为未关注
    update_wxuser_subscribe_sql = text("""
        update wxuser set subscribe = 0 where openid = :openid;
    """)

    with DB() as session:
        result = session.execute(select_binded_wx_user_sql.format(tag_id=all_tags['已绑定'])).fetchall()
        # 按省份归类
        group_data = groupby(result, key=lambda x: x.prov)

        # 遍历省份信息
        for key, group in group_data:
            # 如果key不在已创建标签中, 则先创建
            if not key:
                continue
            if key not in all_tags:
                new_tag = create_user_tags(key)
                all_tags[new_tag['name']] = new_tag['id']
            openid_list = []
            for each in group:
                # 更新备注
                try:
                    update_wxuser_remark(each.openid, each.mobile)
                except WeChatClientException as e:
                    if e.errcode == '43004':
                        # 用户已取消关注, 更新本地
                        session.execute(
                            update_wxuser_subscribe_sql,
                            {
                                'openid': each.openid,
                            })
                    continue

                openid_list.append(each.openid)
            # 批量加上已绑定的标签, 省份的标签
            for i in range(0, len(openid_list), 50):
                openids = openid_list[i:i + 50]
                try:
                    ret = update_users_tag(openids, all_tags[key])
                    if ret['errcode'] == 0:
                        session.execute(
                            update_wxuser_info_sql,
                            {
                                'tag_id': all_tags[key],
                                'openids': tuple(openids),
                            })
                except Exception as e:
                    print(e)

                try:
                    ret = update_users_tag(openids, all_tags['已绑定'])
                    if ret['errcode'] == 0:
                        session.execute(
                            update_wxuser_info_sql,
                            {
                                'tag_id': all_tags['已绑定'],
                                'openids': tuple(openids),
                            })
                except Exception as e:
                    print(e)


if __name__ == '__main__':
    a = get_timestamp_range(3)
    print(a)
    print(int(a.end) - int(a.start))
    # invite_binding(1)
    # first_invite_binding()
    # invite_improve()
    # pack_ending_notification(1)
    # pack_ended_notification()
    sync_binded_wx_user()
